﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public class StrategyPlanBLL : IStrategyPlanBLL
    {
        DBManager dbManager;
        public StrategyPlanBLL()
        {

        }

        public int Insert(int MapSeq, int ListSeq, int? UpperMapSeq, int Year, string Status, string Code,
            int CreatedBy, DateTime CreatedWhen)
        {
            try
            {
                int Tmp = GetStratMapSeq(MapSeq);
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@bgm_strat_map_seq", Tmp);
                dbManager.AddParameters(1, "@bgm_strat_list_seq", ListSeq);
                dbManager.AddParameters(2, "@upper_bgm_strat_data_map_seq", UpperMapSeq);
                dbManager.AddParameters(3, "@bgm_year", Year);
                dbManager.AddParameters(4, "@record_status", Status);
                dbManager.AddParameters(5, "@created_by", CreatedBy);
                dbManager.AddParameters(6, "@created_when", DateTime.Now);
                dbManager.AddParameters(7, "@bgm_strat_list_code", Code);
                string strsql = "Insert into bgm_strategy_data_mapping (" +
                                "bgm_strat_map_seq," +
                                "bgm_strat_list_seq," +
                                "bgm_strat_list_code," +
                                "upper_bgm_strat_data_map_seq," +
                                "bgm_year," +
                                "record_status," +
                                "created_by," +
                                "created_when)" +
                                " values(" +
                                "@bgm_strat_map_seq," +
                                "@bgm_strat_list_seq," +
                                "@bgm_strat_list_code," +
                                "@upper_bgm_strat_data_map_seq," +
                                "@bgm_year," +
                                "@record_status," +
                                "@created_by," +
                                "@created_when)";
                return dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update()
        {

        }

        public void Delete(int Seq)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "Seq", Seq);
                string strsql = "DELETE FROM bgm_strategy_data_mapping WHERE bgm_strat_data_map_seq = @Seq";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public void Approve(string bgm_year, string UpdatedBy)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@updated_when", DateTime.Now);
                string strsql = "update bgm_strategy_data_mapping"
                    + " set record_status = 'A'"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " where bgm_year = @bgm_year"
                    + " and record_status not like 'D'";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetStrategyPlanByYear(int Year)
        {       
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "Year", Year);
                dbManager.AddParameters(1, "status", SystemInfo._DeleteRecordStatus);
                string strsql = "SELECT " +
                                "bgm_mas_strategy_mapping.bgm_strat_type_seq," +
                                "bgm_mas_strategy_mapping.bgm_strat_map_seq," +
                                "bgm_mas_strategy_mapping.upper_bgm_strat_map_seq," +
                                "bgm_mas_strategy_type.bgm_strat_type_name," +
                                "bgm_strategy_data_mapping.bgm_strat_data_map_seq," +
                                "bgm_strategy_data_mapping.upper_bgm_strat_data_map_seq," +
                                "bgm_strategy_data_list.bgm_strat_list_name," +
                                "CONCAT(coalesce(bgm_strategy_data_mapping.bgm_strat_list_code, ''), ' : ', bgm_mas_strategy_type.bgm_strat_type_name, ' : ' ,bgm_strategy_data_list.bgm_strat_list_name) as Full_strategy_Name" +
                                " FROM" +
                                " bgm_mas_strategy_type" +
                                " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                                " Inner Join bgm_strategy_data_mapping ON bgm_strategy_data_mapping.bgm_strat_map_seq = bgm_mas_strategy_mapping.bgm_strat_map_seq" +
                                " Inner Join bgm_strategy_data_list ON bgm_strategy_data_mapping.bgm_strat_list_seq = bgm_strategy_data_list.bgm_strat_list_seq"+
                                " WHERE bgm_strategy_data_mapping.bgm_year =  @Year"
                                + " and bgm_strategy_data_mapping.record_status not like @status";

                return dbManager.ExecuteDataSet(CommandType.Text,strsql).Tables[0];
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public DataTable GetStrategyTypeForCombobox(int StratrgyDataMapSeq,string AddType)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "StratrgyDataMapSeq", StratrgyDataMapSeq);
                string strsql;
                
                if(AddType == "F_Root")
                {
                    strsql = "SELECT " +
                            " bgm_mas_strategy_type.bgm_strat_type_seq, " +
                            " bgm_mas_strategy_type.bgm_strat_type_name " +
                            " FROM " +
                            " bgm_mas_strategy_type " +
                            " WHERE " +
                            " bgm_mas_strategy_type.bgm_strat_type_seq =  @StratrgyDataMapSeq";
                }
                else if (AddType == "P")
                {
                    strsql = "SELECT " +
                            " bgm_mas_strategy_type.bgm_strat_type_seq, " +
                            " bgm_mas_strategy_type.bgm_strat_type_name " +
                            " FROM  " +
                            " bgm_mas_strategy_type " +
                            " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq " +
                            " WHERE  " +
                            " COALESCE(bgm_mas_strategy_mapping.upper_bgm_strat_map_seq,0) = ( " +
                            " SELECT " +
                            " COALESCE(bgm_mas_strategy_mapping.upper_bgm_strat_map_seq,0) as upper_bgm_strat_map_seq " +
                            " FROM  " +
                            " bgm_mas_strategy_type " +
                            " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq " +
                            " WHERE  " +
                            " bgm_mas_strategy_mapping.bgm_strat_map_seq = ( " +
                            " SELECT  " +
                            " bgm_strategy_data_mapping.bgm_strat_map_seq " +
                            " FROM  " +
                            " bgm_mas_strategy_type " +
                            " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq " +
                            " Inner Join bgm_strategy_data_mapping ON bgm_strategy_data_mapping.bgm_strat_map_seq = bgm_mas_strategy_mapping.bgm_strat_map_seq " +
                            " WHERE  " +
                            " bgm_strategy_data_mapping.bgm_strat_data_map_seq=  @StratrgyDataMapSeq))";
                }
                else 
                {
                    strsql ="SELECT " +
                            "bgm_mas_strategy_type.bgm_strat_type_seq," +
                            "bgm_mas_strategy_type.bgm_strat_type_name" +
                            " FROM " +
                            "bgm_mas_strategy_type" +
                            " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                            " WHERE " +
                            "bgm_mas_strategy_mapping.upper_bgm_strat_map_seq =  (" +
                            " SELECT " +
                            "bgm_mas_strategy_mapping.bgm_strat_map_seq" +
                            " FROM " +
                            "bgm_mas_strategy_type" +
                            " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                            " Inner Join bgm_strategy_data_mapping ON bgm_strategy_data_mapping.bgm_strat_map_seq = bgm_mas_strategy_mapping.bgm_strat_map_seq" +
                            " WHERE " +
                            "bgm_strategy_data_mapping.bgm_strat_data_map_seq =  @StratrgyDataMapSeq )";
                }
                return dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }


        //bgm_strategy_data_list
        public DataTable GetStrategyBySequence(int Sequence)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "seq", Sequence);
                dbManager.AddParameters(1, "record_status", SystemInfo._DeleteRecordStatus);
                string strsql = "SELECT dl.*, dm.*" +
                                " FROM bgm_strategy_data_list dl" +
                                " Inner Join bgm_strategy_data_mapping dm on dl.bgm_strat_list_seq = dm.bgm_strat_list_seq" +
                                " WHERE dl.record_status not like @record_status and dm.bgm_strat_data_map_seq = @seq";
                return dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public DataTable GetStrategyDataListForCombobox(int StratrgyType,int Year)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "StratrgyType", StratrgyType);
                dbManager.AddParameters(1, "Year", Year);
                //string strsql = "SELECT " +
                //                "bgm_strategy_data_list.bgm_strat_list_seq," +
                //                "bgm_strategy_data_list.bgm_strat_list_name" +
                //                " FROM " +
                //                "bgm_strategy_data_list" +
                //                " WHERE " +
                //                "bgm_strategy_data_list.bgm_strat_type_seq = @StratrgyType";
                string strsql = "SELECT bgm_strat_list_seq, bgm_strat_list_name"
                    + " FROM bgm_strategy_data_list"
                    + " WHERE record_status not like 'D'"
                        + " AND bgm_strat_type_seq = @StratrgyType"
                        + " AND bgm_strat_list_seq not in"
                            + " (SELECT bgm_strat_list_seq"
                            + " FROM bgm_strategy_data_mapping dt"
                            + " Inner Join bgm_mas_strategy_mapping map ON dt.bgm_strat_map_seq = map.bgm_strat_map_seq"
                            + " WHERE dt.record_status not like 'D'"
                            + " and map.bgm_strat_type_seq = @StratrgyType"
                            + " AND dt.bgm_year = @Year)"
                        + " and year_start <= @Year"
                        + " and year_end >= @Year";
                return dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public string[] GetParentStrategyTypeName(int StratrgyDataMapSeq)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "StratrgyDataMapSeq", StratrgyDataMapSeq);
                string strsql = "SELECT " +
                            "bgm_strategy_data_mapping.bgm_strat_data_map_seq," +
                            "bgm_mas_strategy_type.bgm_strat_type_name," +
                            "bgm_strategy_data_list.bgm_strat_list_name" +
                            " FROM " +
                            "bgm_mas_strategy_type" +
                            " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                            " Inner Join bgm_strategy_data_mapping ON bgm_strategy_data_mapping.bgm_strat_map_seq = bgm_mas_strategy_mapping.bgm_strat_map_seq" +
                            " Inner Join bgm_strategy_data_list ON bgm_strategy_data_mapping.bgm_strat_list_seq = bgm_strategy_data_list.bgm_strat_list_seq" +
                            " WHERE " +
                            "bgm_strategy_data_mapping.bgm_strat_data_map_seq =  (" +
                            " SELECT " +
                            "bgm_strategy_data_mapping.upper_bgm_strat_data_map_seq" +
                            " FROM " +
                            "bgm_mas_strategy_type" +
                            " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                            " Inner Join bgm_strategy_data_mapping ON bgm_strategy_data_mapping.bgm_strat_map_seq = bgm_mas_strategy_mapping.bgm_strat_map_seq" +
                            " WHERE " +
                            "bgm_strategy_data_mapping.bgm_strat_data_map_seq =  @StratrgyDataMapSeq )";
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
                return new string[] { dt.Rows[0]["bgm_strat_data_map_seq"].ToString(),
                dt.Rows[0]["bgm_strat_type_name"].ToString() + " : " + dt.Rows[0]["bgm_strat_list_name"].ToString()};
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public int CheckAddChild(int StratrgyDataMapSeq)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "StratrgyDataMapSeq", StratrgyDataMapSeq);
                string strsql = "SELECT " +
                    " bgm_mas_strategy_mapping.bgm_strat_map_seq," +
                    " bgm_mas_strategy_mapping.bgm_strat_type_seq," +
                    " bgm_mas_strategy_mapping.upper_bgm_strat_map_seq," +
                    " bgm_mas_strategy_type.bgm_strat_type_name" +
                    " FROM " +
                    " bgm_mas_strategy_type" +
                    " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                    " WHERE " +
                    " bgm_mas_strategy_mapping.upper_bgm_strat_map_seq =  (" +
                    " SELECT " +
                    " bgm_strategy_data_mapping.bgm_strat_map_seq" +
                    " FROM " +
                    " bgm_mas_strategy_type" +
                    " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                    " Inner Join bgm_strategy_data_mapping ON bgm_strategy_data_mapping.bgm_strat_map_seq = bgm_mas_strategy_mapping.bgm_strat_map_seq" +
                    " WHERE " +
                    " bgm_strategy_data_mapping.bgm_strat_data_map_seq =  @StratrgyDataMapSeq)";
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
                return dt.Rows.Count;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public int CheckAddParent(int StratrgyDataMapSeq)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "StratrgyDataMapSeq", StratrgyDataMapSeq);
                string strsql = " SELECT" +
                                " bgm_mas_strategy_mapping.bgm_strat_map_seq," +
                                " bgm_mas_strategy_mapping.bgm_strat_type_seq," +
                                " bgm_mas_strategy_mapping.upper_bgm_strat_map_seq," +
                                " bgm_mas_strategy_type.bgm_strat_type_name" +
                                " FROM" +
                                " bgm_mas_strategy_type" +
                                " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                                " WHERE " +
                                " bgm_mas_strategy_mapping.bgm_strat_map_seq =  (" +
                                " SELECT " +
                                " bgm_strategy_data_mapping.bgm_strat_map_seq" +
                                " FROM " +
                                " bgm_mas_strategy_type" +
                                " Inner Join bgm_mas_strategy_mapping ON bgm_mas_strategy_mapping.bgm_strat_type_seq = bgm_mas_strategy_type.bgm_strat_type_seq" +
                                " Inner Join bgm_strategy_data_mapping ON bgm_strategy_data_mapping.bgm_strat_map_seq = bgm_mas_strategy_mapping.bgm_strat_map_seq" +
                                " WHERE " +
                                " bgm_strategy_data_mapping.bgm_strat_data_map_seq =  @StratrgyDataMapSeq) AND bgm_mas_strategy_mapping.upper_bgm_strat_map_seq IS NOT NULL";
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
                return dt.Rows.Count;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public int GetStratMapSeq(int StratDataMapSeq)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "StratDataMapSeq", StratDataMapSeq);
                string strsql = " SELECT bgm_strat_map_seq FROM bgm_mas_strategy_mapping WHERE bgm_strat_type_seq = @StratDataMapSeq";
                //string strsql = " SELECT bgm_strat_map_seq FROM bgm_mas_strategy_mapping WHERE upper_bgm_strat_map_seq = @StratDataMapSeq";
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
                return (int)dt.Rows[0][0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string CloneStrategyPlanByYear(int CloneFromYear,int ToYear, int CreateBy)
        {
            try
            {
                DataTable dt = this.GetStrategyPlanByYear(CloneFromYear);
                if (dt.Rows.Count <= 0)
                    return "NoData";
                if (!DeleteStrategyPlanByYear(ToYear))
                    return "CanNotDelete";
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "cloneYear", ToYear);
                dbManager.AddParameters(1, "usr", CreateBy);
                dbManager.AddParameters(2, "preYear", CloneFromYear);
                string StrSql = "select F_Auto_Clone_Strategy_Data_Mapping(@preYear,@cloneYear,@usr)";
                //dbManager.ExecuteDataSet(CommandType.Text,StrSql);
                //int tmp = (int)dt.Rows[0][0];
                int tmp = dbManager.ExecuteNonQuery(CommandType.Text, StrSql);
                //dbManager.ExecuteScalar(CommandType.Text, StrSql);
                //if (tmp <= 0)
                //    return "Error";
                //if (tmp > 0)
                return "OK";
                //return "";
            }
            catch
            {
                return "Error";
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        private Boolean DeleteStrategyPlanByYear(int Year)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "Year", Year);
                string strsql = "DELETE FROM bgm_strategy_data_mapping WHERE bgm_year = @Year";
                if (dbManager.ExecuteNonQuery(CommandType.Text, strsql) >= 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public int GetUpperStratMapSeq(int StratDataMapSeq)
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "StratDataMapSeq", StratDataMapSeq);
                string strsql = " SELECT upper_bgm_strat_data_map_seq FROM bgm_strategy_data_mapping WHERE bgm_strat_data_map_seq = @StratDataMapSeq";
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
                return (int)dt.Rows[0][0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public int GetRootStrategyTypeSeq()
        {
            try
            {
                dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "status", SystemInfo._ActiveRecordStatus);

                string strsql = "SELECT bgm_strat_type_seq FROM bgm_mas_strategy_mapping WHERE upper_bgm_strat_map_seq is null";
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0];
                return (int)dt.Rows[0][0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }
    }
}
